package spatialmarketplace.cem.dao.implementation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import net.sourceforge.stripes.util.Log;
import spatialmarketplace.cem.dao.DBHandler;
import spatialmarketplace.cem.dao.GroupDao;
import spatialmarketplace.cem.model.Group;
import spatialmarketplace.cem.model.GroupMember;
import spatialmarketplace.cem.model.User;

/**
 * Class that implements DAO related to the Group
 *
 * @author Laura Andreina
 */
public class GroupDaoImpl implements GroupDao {

    private static final Log logger = Log.getInstance(GroupDaoImpl.class);
    private Connection conn = null;
    private Map<Integer, Group> map = new HashMap<Integer, Group>();
    private static GroupDaoImpl instance = new GroupDaoImpl();

    public static GroupDaoImpl getInstance() {
        return instance;
    }

    /**
     * Method to get all the groups by default and show them in the welcome
     * screen - search area
     *
     * @param id
     * @return
     */
    public List<Group> read(Integer id) {
        DBHandler dbInstance = DBHandler.getInstance();
        List<Group> list = new ArrayList<Group>();
        Group group;
        Integer groupId;
        String name;
        boolean isPublic;
        boolean memberCanInvite;
        boolean forumIsPublic;
        boolean memberCanPublishResources;
        boolean adsEnabled;
        String logo;
        String description;
        String accessType;
        String keyword;

        try {
            if (dbInstance != null) {
                String query = new String("SELECT c.groupId AS groupId, c.name AS NAME, c.isPublic AS isPublic, c.memberCanInvite AS memberCanInvite,"
                        + "c.forumIsPublic AS forumIsPublic, c.memberCanPublishResources AS memberCanPublishResources, "
                        + "c.adsEnabled AS adsEnabled, c.logo AS logo, c.accessType AS accessType, c.keyword AS keyword,"
                        + "c.description AS description "
                        + "FROM CEM_Group c "
                        + "WHERE c.groupId NOT IN (SELECT groupId FROM CEM_GroupMember WHERE userId=" + id + ")"
                        + "ORDER BY c.groupId DESC");

                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        groupId = Integer.valueOf(rs.getString("groupId"));
                        name = rs.getString("name");
                        isPublic = Boolean.getBoolean(rs.getString("isPublic"));
                        memberCanInvite = Boolean.getBoolean(rs.getString("memberCanInvite"));
                        forumIsPublic = Boolean.getBoolean(rs.getString("forumIsPublic"));
                        memberCanPublishResources = Boolean.getBoolean(rs.getString("memberCanPublishResources"));
                        adsEnabled = Boolean.getBoolean(rs.getString("adsEnabled"));
                        logo = rs.getString("logo");
                        description = rs.getString("description");
                        accessType = rs.getString("accessType");
                        keyword = rs.getString("keyword");

                        group = new Group(groupId, name, isPublic, memberCanInvite, forumIsPublic, memberCanPublishResources,
                                adsEnabled, logo, description, accessType, keyword);

                        list.add(group);

                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
        }

        return list;
    }

       /**
     * Method to search groups in welcome screen
     *
     * @param keywords
     * @return
     */
    public List<Group> search(String keywords, Integer userId) {
        DBHandler dbInstance = DBHandler.getInstance();
        List<Group> groupSearched = new ArrayList<Group>();
        Group group;
        Integer groupId;
        String name;
        boolean isPublic;
        boolean memberCanInvite;
        boolean forumIsPublic;
        boolean memberCanPublishResources;
        boolean adsEnabled;
        String logo;
        String description;
        String accessType;
        String keyword;

        try {
            if (dbInstance != null) {
                String query = new String("select groupId, name, isPublic, memberCanInvite,forumIsPublic,memberCanPublishResources, "
                        + "adsEnabled, logo, accessType, keyword, description "
                        + "from CEM_Group where name like '%" + keywords + "%' "
                        + "AND groupId NOT IN (SELECT groupId FROM CEM_GroupMember WHERE userId=" + userId + ")");

                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        /*
                         * get the results
                         */
                        groupId = Integer.valueOf(rs.getString("groupId"));
                        name = rs.getString("name");
                        isPublic = Boolean.getBoolean(rs.getString("isPublic"));
                        memberCanInvite = Boolean.getBoolean(rs.getString("memberCanInvite"));
                        forumIsPublic = Boolean.getBoolean(rs.getString("forumIsPublic"));
                        memberCanPublishResources = Boolean.getBoolean(rs.getString("memberCanPublishResources"));
                        adsEnabled = Boolean.getBoolean(rs.getString("adsEnabled"));
                        logo = rs.getString("logo");
                        description = rs.getString("description");
                        accessType = rs.getString("accessType");
                        keyword = rs.getString("keyword");

                        group = new Group(groupId, name, isPublic, memberCanInvite, forumIsPublic, memberCanPublishResources,
                                adsEnabled, logo, description, accessType, keyword);

                        groupSearched.add(group);

                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
        }


        return groupSearched;
    }

    /**
     * Method to get the list of users to be displayed in create group
     *
     * @param id
     * @return
     */
    public List<User> readUsers(Integer id) {
        System.out.println("bbbbbb " + id);
        DBHandler dbInstance = DBHandler.getInstance();
        List<User> users = new ArrayList<User>();
        User user;
        Integer userId;
        String userName;
        String photo;
        String profileDesc;
        String position;
        String contactNumber;
        String email;


        try {
            if (dbInstance != null) {
                String query = new String("SELECT * FROM SM_User WHERE userId != " + id);
                System.out.println("bbbbbb query " + query);
                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        userId = Integer.valueOf(rs.getString("userId"));
                        userName = rs.getString("userName");
                        photo = rs.getString("photo");
                        profileDesc = rs.getString("profileDescription");
                        position = rs.getString("position");
                        contactNumber = rs.getString("contactNumber");
                        email = rs.getString("email");

                        user = new User(userId, userName, photo, profileDesc, position, contactNumber, email);

                        users.add(user);
                        //System.out.println("-------------------" + user.getUserId() + ", " + user.getUserName());
                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
        }
        return users;
    }

    /**
     * Methods to save a new group created
     *
     * @param group
     * @return
     */
    public Integer save(Group group) {
        DBHandler dbInstance = DBHandler.getInstance();
        Integer groupId = null;
        try {
            if (dbInstance != null) {

                String query = new String("INSERT INTO  CEM_Group (name, isPublic, memberCanInvite, forumIsPublic, "
                        + "memberCanPublishResources, adsEnabled, logo, accessType, keyword, description)"
                        + " values (?, ?, ?, ?, ? , ?, ?, ?, ?, ?)");
                conn = dbInstance.openConnection();

                try {
                    PreparedStatement preparedStatement = null;
                    // PreparedStatements can use variables and are more efficient
                    preparedStatement = conn.prepareStatement(query);
                    // Parameters start with 1
                    preparedStatement.setString(1, group.getName());
                    preparedStatement.setString(2, String.valueOf(group.isIsPublic() ? 1 : 0));
                    preparedStatement.setString(3, String.valueOf(group.isMemberCanInvite() ? 1 : 0));
                    preparedStatement.setString(4, String.valueOf(group.isForumIsPublic() ? 1 : 0));
                    preparedStatement.setString(5, String.valueOf(group.isMemberCanPublishResources() ? 1 : 0));
                    preparedStatement.setString(6, String.valueOf(group.isAdsEnabled() ? 1 : 0));
                    preparedStatement.setString(7, group.getLogo());
                    preparedStatement.setString(8, group.getAccessType());
                    preparedStatement.setString(9, group.getKeyword());
                    preparedStatement.setString(10, group.getDescription());

                    preparedStatement.execute();

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                    return groupId;
                }

            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
            return groupId;
        }
        // fetch the latest groupId inserted
        groupId = fetchGroupId();
        return groupId;

    }

    /**
     * Method to get the latest groupId inserted when a group is created in
     * order to associate the userId with the groupId of the group created
     *
     * @return
     */
    public Integer fetchGroupId() {
        DBHandler dbInstance = DBHandler.getInstance();
        Integer id = null;

        try {
            if (dbInstance != null) {
                String query = new String("SELECT MAX(groupId) as groupId FROM CEM_Group");
                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        id = Integer.valueOf(rs.getString("groupId"));
                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
        }
        return id;


    }

    /**
     * Method to save the list of members added to a new group
     *
     * @param users
     * @param groupId
     */
    public void saveGroupMembers(List<User> users, Integer groupId, Integer currentUserId) {
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                String query = new String("INSERT INTO CEM_GroupMember (userId, isAdmin, isModerator, linkedInIsPublic, profileDescriptionIsPublic, "
                        + "positionIsPublic, contactNumberIsPublic, emailIsPublic, nameIsPublic, linkedInProfile, groupId) "
                        + "values(?,?,?,?,?,?,?,?,?,?,?)");
                conn = dbInstance.openConnection();
                try {
                    Iterator i = users.iterator();
                    User user;
                    PreparedStatement preparedStatement = null;
                    // PreparedStatements can use variables and are more efficient
                    preparedStatement = conn.prepareStatement(query);
                    // Parameters start with 1
                    while (i.hasNext()) {
                        user = new User();
                        user = (User) i.next();
                        preparedStatement.setInt(1, user.getUserId());
                        if(user.getUserId().equals(currentUserId)){
                            preparedStatement.setString(2, String.valueOf("1"));
                        }else{
                            preparedStatement.setString(2, String.valueOf("0"));
                        }
                        preparedStatement.setString(3, String.valueOf("0"));
                        preparedStatement.setString(4, String.valueOf("0"));
                        preparedStatement.setString(5, String.valueOf("0"));
                        preparedStatement.setString(6, String.valueOf("0"));
                        preparedStatement.setString(7, String.valueOf("0"));
                        preparedStatement.setString(8, String.valueOf("0"));
                        preparedStatement.setString(9, String.valueOf("0"));
                        preparedStatement.setString(10, String.valueOf("0"));
                        preparedStatement.setInt(11, groupId);

                        preparedStatement.execute();
                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);

                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);

        }

    }

    /**
     * Method to join in a group
     *
     * @param groupId
     * @param userId
     */
    @Override
    public void joinGroup(Integer groupId, Integer userId) {
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                String query = new String("INSERT INTO CEM_GroupMember (userId, isAdmin, isModerator, linkedInIsPublic, profileDescriptionIsPublic, "
                        + "positionIsPublic, contactNumberIsPublic, emailIsPublic, nameIsPublic, linkedInProfile, groupId) "
                        + "values(?,?,?,?,?,?,?,?,?,?,?)");
                conn = dbInstance.openConnection();
                try {
                    PreparedStatement preparedStatement = null;
                    // PreparedStatements can use variables and are more efficient
                    preparedStatement = conn.prepareStatement(query);
                    // Parameters start with 1
                    preparedStatement.setInt(1, userId);
                    preparedStatement.setString(2, String.valueOf("0"));
                    preparedStatement.setString(3, String.valueOf("0"));
                    preparedStatement.setString(4, String.valueOf("0"));
                    preparedStatement.setString(5, String.valueOf("0"));
                    preparedStatement.setString(6, String.valueOf("0"));
                    preparedStatement.setString(7, String.valueOf("0"));
                    preparedStatement.setString(8, String.valueOf("0"));
                    preparedStatement.setString(9, String.valueOf("0"));
                    preparedStatement.setString(10, String.valueOf("0"));
                    preparedStatement.setInt(11, groupId);

                    preparedStatement.execute();

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);

                }

            }

        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);

        }
    }

    /**
     * Method to get my group list on welcome screen
     *
     * @param id
     * @return
     */
    public List<Group> getMyGroups(Integer id) {
        DBHandler dbInstance = DBHandler.getInstance();
        List<Group> groupList = new ArrayList<Group>();
        Group group;
        List<User> users;
        User user;
        Integer isAdmin;
        String position;
        try {
            if (dbInstance != null) {
                String query = new String("SELECT g.groupId as groupId, g.name as name, g.logo as logo, u.userId as userId, gm.isAdmin AS position "
                        + "FROM CEM_Group g, CEM_GroupMember gm, SM_User u "
                        + "WHERE g.groupId=gm.groupId AND gm.userId=u.userId AND gm.userId=" + id + " order by groupId desc");
                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        /*
                         * get the results
                         */
                        group = new Group();
                        user = new User();
                        users = new ArrayList<User>();
                        group.setGroupId(Integer.valueOf(rs.getString("groupId")));
                        group.setName(rs.getString("name"));
                        group.setLogo(rs.getString("logo"));
                        user.setUserId(Integer.valueOf(rs.getString("userId")));
                        isAdmin = Integer.valueOf(rs.getString("position"));
                        if (isAdmin.equals(1)) {
                            position = "Administrator";
                        } else {
                            position = "Member";
                        }
                        user.setPosition(position);
                        users.add(user);
                        group.setUsers(users);
                        //add to the group list
                        groupList.add(group);

                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
        }


        return groupList;


    }

    /**
     * Method to get member list related to a group
     *
     * @param groupId
     * @return
     */
    public List<GroupMember> retrieveGroupMembers(Integer groupId, Integer usrId) {
        DBHandler dbInstance = DBHandler.getInstance();
        List<GroupMember> memberList = new ArrayList<GroupMember>();
        GroupMember groupMember;
        String memberRol;
        Integer isAdmin;

        try {
            if (dbInstance != null) {
                String query = new String("SELECT g.userId as userId, g.isAdmin as isAdmin, g.groupId as groupId, u.userName as userName, u.photo as photo "
                        + "FROM CEM_GroupMember g, SM_User u "
                        + "WHERE g.userId=u.userId AND groupId=" + groupId + " AND g.userId != "+ usrId);
                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        /*
                         * get the results
                         */
                        groupMember = new GroupMember();
                        groupMember.setUserId(Integer.valueOf(rs.getString("userId")));
                        groupMember.setUserName(rs.getString("userName"));
                        isAdmin = Integer.valueOf(rs.getString("isAdmin"));
                        if (isAdmin.equals(1)) {
                            memberRol = "Administrator";
                        } else {
                            memberRol = "Member";
                        }
                        groupMember.setRol(memberRol);
                        groupMember.setPhoto(rs.getString("photo"));
                        groupMember.setGroupId(Integer.valueOf(rs.getString("groupId")));

                        //add to the group list
                        memberList.add(groupMember);

                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
        }


        return memberList;
    }

    /**
     * to get the notification list to be shown in the dashboard
     *
     * @return
     */
    @Override
    public void retrieveNotificationList() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     * Method to retrieve a group name given the groupId
     *
     * @param groupId
     * @return
     */
    @Override
    public String fetchGroupName(Integer groupId) {
        DBHandler dbInstance = DBHandler.getInstance();
        String groupName = "";

        try {
            if (dbInstance != null) {
                String query = new String("SELECT name FROM CEM_Group WHERE groupId=" + groupId);
                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        /*
                         * get the results
                         */
                        groupName = rs.getString("name");

                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
        }


        return groupName;

    }

    /**
     * Method to get the member details in the dashboard
     *
     * @param userId
     * @param groupId
     * @return
     */
    public User memberDetails(Integer userId, Integer groupId) {
        DBHandler dbInstance = DBHandler.getInstance();
        User user = new User();
        Integer usrId;
        String userName;
        String photo;
        String profileDescription;
        String position;
        String contactNumber;
        String email;
        Integer islinkedInPublic;
        String linkedIn;

        try {
            if (dbInstance != null) {
                String query = new String("SELECT g.userid AS userId, g.isadmin AS isAdmin, g.linkedInIsPublic AS linkedInIsPublic, "
                        + " g.profileDescriptionIsPublic AS profileDescriptionIsPublic, g.positionIsPublic AS positionIsPublic, "
                        + "g.contactNumberIsPublic AS contactNumberIsPublic, g.emailIsPublic AS emailIsPublic, "
                        + "g.nameIsPublic AS nameIsPublic, g.linkedInProfile AS linkedInProfile, g.groupId AS groupId, "
                        + "u.userName AS userName, u.photo AS photo, u.profileDescription AS profileDescription, u.position as position, "
                        + "u.contactNumber AS contactNumber, u.email AS email "
                        + "FROM CEM_GroupMember g, SM_User u "
                        + "WHERE g.userId=" + userId + " AND g.userId=u.userId AND g.groupId=" + groupId);

                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);
                System.out.println("Query: " + query);

                try {
                    while (rs.next()) {
                        /*
                         * get the results
                         */
                        user = new User();
                        userId = Integer.valueOf(rs.getString("userId"));
                        userName = rs.getString("userName");
                        photo = rs.getString("photo");
                        profileDescription = rs.getString("profileDescription");
                        position = rs.getString("position");
                        contactNumber = rs.getString("contactNumber");
                        email = rs.getString("email");
                        islinkedInPublic = Integer.valueOf(rs.getString("isAdmin"));
                        if (islinkedInPublic.equals(1)) {
                            //set linkedin
                            linkedIn = userName;
                        } else {
                            linkedIn = "Private";
                        }
                        user = new User(userId, userName, photo, profileDescription, position, contactNumber, email);

                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + GroupDaoImpl.class.getName() + ", " + e);
        }
        return user;
    }
}
